package za.co.smartmi.dao.impl;

import za.co.smartmi.dao.ProductDao;
import za.co.smartmi.model.Product;
import za.co.smartmi.model.ProductPk;
import za.co.smartmi.dao.exceptions.ProductDaoException;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import za.co.smartmi.dao.support.JdbcGenericDao;

@Repository
public class ProductDaoImpl extends JdbcGenericDao implements ParameterizedRowMapper<Product>, ProductDao
{
    
        @Autowired
        public void setDataSource(DataSource dataSource) {
                setJdbcTemplate(new JdbcTemplate(dataSource));
	}
        
	/**
	 * Method 'insert'
	 * 
	 * @param dto
	 * @return ProductPk
	 */
	@Transactional
	public ProductPk insert(Product dto)
	{
		getJdbcTemplate().update("INSERT INTO " + getTableName() + " ( productid, name, desc, type, cover, premium, adminfee, coverfee, vatfee, otherfee, status, startdate, enddate, csys, cdate, usys, udate ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )",dto.getProductid(),dto.getName(),dto.getDesc(),dto.getType(),dto.getCover(),dto.getPremium(),dto.getAdminfee(),dto.getCoverfee(),dto.getVatfee(),dto.getOtherfee(),dto.getStatus(),dto.getStartdate(),dto.getEnddate(),dto.getCsys(),dto.getCdate(),dto.getUsys(),dto.getUdate());
		return dto.createPk();
	}

	/** 
	 * Updates a single row in the product table.
	 */
	@Transactional
	public void update(ProductPk pk, Product dto) throws ProductDaoException
	{
		getJdbcTemplate().update("UPDATE " + getTableName() + " SET productid = ?, name = ?, desc = ?, type = ?, cover = ?, premium = ?, adminfee = ?, coverfee = ?, vatfee = ?, otherfee = ?, status = ?, startdate = ?, enddate = ?, csys = ?, cdate = ?, usys = ?, udate = ? WHERE productid = ?",dto.getProductid(),dto.getName(),dto.getDesc(),dto.getType(),dto.getCover(),dto.getPremium(),dto.getAdminfee(),dto.getCoverfee(),dto.getVatfee(),dto.getOtherfee(),dto.getStatus(),dto.getStartdate(),dto.getEnddate(),dto.getCsys(),dto.getCdate(),dto.getUsys(),dto.getUdate(),pk.getProductid());
	}

	/** 
	 * Deletes a single row in the product table.
	 */
	@Transactional
	public void delete(ProductPk pk) throws ProductDaoException
	{
		getJdbcTemplate().update("DELETE FROM " + getTableName() + " WHERE productid = ?",pk.getProductid());
	}

	/**
	 * Method 'mapRow'
	 * 
	 * @param rs
	 * @param row
	 * @throws SQLException
	 * @return Product
	 */
	public Product mapRow(ResultSet rs, int row) throws SQLException
	{
		Product dto = new Product();
		dto.setProductid( new Integer( rs.getInt(1) ) );
		dto.setName( rs.getString( 2 ) );
		dto.setDesc( rs.getString( 3 ) );
		dto.setType( rs.getString( 4 ) );
		dto.setCover( rs.getBigDecimal(5));
		dto.setPremium( rs.getBigDecimal(6));
		dto.setAdminfee( rs.getBigDecimal(7));
		dto.setCoverfee( rs.getBigDecimal(8));
		dto.setVatfee( rs.getBigDecimal(9));
		dto.setOtherfee( rs.getBigDecimal(10));
		dto.setStatus( rs.getString( 11 ) );
		dto.setStartdate( rs.getDate(12 ) );
		dto.setEnddate( rs.getDate(13 ) );
		dto.setCsys( rs.getString( 14 ) );
		dto.setCdate( rs.getDate(15 ) );
		dto.setUsys( rs.getString( 16 ) );
		dto.setUdate( rs.getDate(17 ) );
		return dto;
	}

	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName()
	{
		return "smartMI.product";
	}

	/** 
	 * Returns all rows from the product table that match the criteria 'productid = :productid'.
	 */
	@Transactional
	public Product findByPrimaryKey(Integer productid) throws ProductDaoException
	{
		try {
			List<Product> list = getJdbcTemplate().query("SELECT productid, name, desc, type, cover, premium, adminfee, coverfee, vatfee, otherfee, status, startdate, enddate, csys, cdate, usys, udate FROM " + getTableName() + " WHERE productid = ?", this,productid);
			return list.size() == 0 ? null : list.get(0);
		}
		catch (Exception e) {
			throw new ProductDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the product table that match the criteria ''.
	 */
	@Transactional
	public List<Product> findAll() throws ProductDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT productid, name, desc, type, cover, premium, adminfee, coverfee, vatfee, otherfee, status, startdate, enddate, csys, cdate, usys, udate FROM " + getTableName() + " ORDER BY productid", this);
		}
		catch (Exception e) {
			throw new ProductDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the product table that match the criteria 'productid = :productid'.
	 */
	@Transactional
	public List<Product> findWhereProductidEquals(Integer productid) throws ProductDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT productid, name, desc, type, cover, premium, adminfee, coverfee, vatfee, otherfee, status, startdate, enddate, csys, cdate, usys, udate FROM " + getTableName() + " WHERE productid = ? ORDER BY productid", this,productid);
		}
		catch (Exception e) {
			throw new ProductDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the product table that match the criteria 'name = :name'.
	 */
	@Transactional
	public List<Product> findWhereNameEquals(String name) throws ProductDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT productid, name, desc, type, cover, premium, adminfee, coverfee, vatfee, otherfee, status, startdate, enddate, csys, cdate, usys, udate FROM " + getTableName() + " WHERE name = ? ORDER BY name", this,name);
		}
		catch (Exception e) {
			throw new ProductDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the product table that match the criteria 'desc = :desc'.
	 */
	@Transactional
	public List<Product> findWhereDescEquals(String desc) throws ProductDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT productid, name, desc, type, cover, premium, adminfee, coverfee, vatfee, otherfee, status, startdate, enddate, csys, cdate, usys, udate FROM " + getTableName() + " WHERE desc = ? ORDER BY desc", this,desc);
		}
		catch (Exception e) {
			throw new ProductDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the product table that match the criteria 'type = :type'.
	 */
	@Transactional
	public List<Product> findWhereTypeEquals(String type) throws ProductDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT productid, name, desc, type, cover, premium, adminfee, coverfee, vatfee, otherfee, status, startdate, enddate, csys, cdate, usys, udate FROM " + getTableName() + " WHERE type = ? ORDER BY type", this,type);
		}
		catch (Exception e) {
			throw new ProductDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the product table that match the criteria 'cover = :cover'.
	 */
	@Transactional
	public List<Product> findWhereCoverEquals(BigDecimal cover) throws ProductDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT productid, name, desc, type, cover, premium, adminfee, coverfee, vatfee, otherfee, status, startdate, enddate, csys, cdate, usys, udate FROM " + getTableName() + " WHERE cover = ? ORDER BY cover", this,cover);
		}
		catch (Exception e) {
			throw new ProductDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the product table that match the criteria 'premium = :premium'.
	 */
	@Transactional
	public List<Product> findWherePremiumEquals(BigDecimal premium) throws ProductDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT productid, name, desc, type, cover, premium, adminfee, coverfee, vatfee, otherfee, status, startdate, enddate, csys, cdate, usys, udate FROM " + getTableName() + " WHERE premium = ? ORDER BY premium", this,premium);
		}
		catch (Exception e) {
			throw new ProductDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the product table that match the criteria 'adminfee = :adminfee'.
	 */
	@Transactional
	public List<Product> findWhereAdminfeeEquals(BigDecimal adminfee) throws ProductDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT productid, name, desc, type, cover, premium, adminfee, coverfee, vatfee, otherfee, status, startdate, enddate, csys, cdate, usys, udate FROM " + getTableName() + " WHERE adminfee = ? ORDER BY adminfee", this,adminfee);
		}
		catch (Exception e) {
			throw new ProductDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the product table that match the criteria 'coverfee = :coverfee'.
	 */
	@Transactional
	public List<Product> findWhereCoverfeeEquals(BigDecimal coverfee) throws ProductDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT productid, name, desc, type, cover, premium, adminfee, coverfee, vatfee, otherfee, status, startdate, enddate, csys, cdate, usys, udate FROM " + getTableName() + " WHERE coverfee = ? ORDER BY coverfee", this,coverfee);
		}
		catch (Exception e) {
			throw new ProductDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the product table that match the criteria 'vatfee = :vatfee'.
	 */
	@Transactional
	public List<Product> findWhereVatfeeEquals(BigDecimal vatfee) throws ProductDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT productid, name, desc, type, cover, premium, adminfee, coverfee, vatfee, otherfee, status, startdate, enddate, csys, cdate, usys, udate FROM " + getTableName() + " WHERE vatfee = ? ORDER BY vatfee", this,vatfee);
		}
		catch (Exception e) {
			throw new ProductDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the product table that match the criteria 'otherfee = :otherfee'.
	 */
	@Transactional
	public List<Product> findWhereOtherfeeEquals(BigDecimal otherfee) throws ProductDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT productid, name, desc, type, cover, premium, adminfee, coverfee, vatfee, otherfee, status, startdate, enddate, csys, cdate, usys, udate FROM " + getTableName() + " WHERE otherfee = ? ORDER BY otherfee", this,otherfee);
		}
		catch (Exception e) {
			throw new ProductDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the product table that match the criteria 'status = :status'.
	 */
	@Transactional
	public List<Product> findWhereStatusEquals(String status) throws ProductDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT productid, name, desc, type, cover, premium, adminfee, coverfee, vatfee, otherfee, status, startdate, enddate, csys, cdate, usys, udate FROM " + getTableName() + " WHERE status = ? ORDER BY status", this,status);
		}
		catch (Exception e) {
			throw new ProductDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the product table that match the criteria 'startdate = :startdate'.
	 */
	@Transactional
	public List<Product> findWhereStartdateEquals(Date startdate) throws ProductDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT productid, name, desc, type, cover, premium, adminfee, coverfee, vatfee, otherfee, status, startdate, enddate, csys, cdate, usys, udate FROM " + getTableName() + " WHERE startdate = ? ORDER BY startdate", this,startdate);
		}
		catch (Exception e) {
			throw new ProductDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the product table that match the criteria 'enddate = :enddate'.
	 */
	@Transactional
	public List<Product> findWhereEnddateEquals(Date enddate) throws ProductDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT productid, name, desc, type, cover, premium, adminfee, coverfee, vatfee, otherfee, status, startdate, enddate, csys, cdate, usys, udate FROM " + getTableName() + " WHERE enddate = ? ORDER BY enddate", this,enddate);
		}
		catch (Exception e) {
			throw new ProductDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the product table that match the criteria 'csys = :csys'.
	 */
	@Transactional
	public List<Product> findWhereCsysEquals(String csys) throws ProductDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT productid, name, desc, type, cover, premium, adminfee, coverfee, vatfee, otherfee, status, startdate, enddate, csys, cdate, usys, udate FROM " + getTableName() + " WHERE csys = ? ORDER BY csys", this,csys);
		}
		catch (Exception e) {
			throw new ProductDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the product table that match the criteria 'cdate = :cdate'.
	 */
	@Transactional
	public List<Product> findWhereCdateEquals(Date cdate) throws ProductDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT productid, name, desc, type, cover, premium, adminfee, coverfee, vatfee, otherfee, status, startdate, enddate, csys, cdate, usys, udate FROM " + getTableName() + " WHERE cdate = ? ORDER BY cdate", this,cdate);
		}
		catch (Exception e) {
			throw new ProductDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the product table that match the criteria 'usys = :usys'.
	 */
	@Transactional
	public List<Product> findWhereUsysEquals(String usys) throws ProductDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT productid, name, desc, type, cover, premium, adminfee, coverfee, vatfee, otherfee, status, startdate, enddate, csys, cdate, usys, udate FROM " + getTableName() + " WHERE usys = ? ORDER BY usys", this,usys);
		}
		catch (Exception e) {
			throw new ProductDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the product table that match the criteria 'udate = :udate'.
	 */
	@Transactional
	public List<Product> findWhereUdateEquals(Date udate) throws ProductDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT productid, name, desc, type, cover, premium, adminfee, coverfee, vatfee, otherfee, status, startdate, enddate, csys, cdate, usys, udate FROM " + getTableName() + " WHERE udate = ? ORDER BY udate", this,udate);
		}
		catch (Exception e) {
			throw new ProductDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns the rows from the product table that matches the specified primary-key value.
	 */
	public Product findByPrimaryKey(ProductPk pk) throws ProductDaoException
	{
		return findByPrimaryKey( pk.getProductid() );
	}

}
